<?php


namespace app\lib;


use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
//use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


class Excel
{
    /**
     * 使用PHPEXECL导入
     *
     * @param string $file 文件地址
     * @param int $sheet 工作表sheet(传0则获取第一个sheet)
     * @param int $columnCnt 列数(传0则自动获取最大列)
     * @param array $options 操作选项
     *                          array mergeCells 合并单元格数组
     *                          array formula    公式数组
     *                          array format     单元格格式数组
     *
     * @return array
     * @throws Exception
     */
    function importExecl($file = '', $sheet = 0, $columnCnt = 0, &$options = [])
    {
        try {
            /* 转码 */
            $file = iconv("utf-8", "gb2312", $file);

            if (empty($file) or !file_exists($file)) {
                $msg['code'] = 0;
                $msg['tips'] = '文件不存在!';
                return $msg;
                //throw new \Exception('文件不存在!');
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader('Xlsx');

            if (!$objRead->canRead($file)) {
                /** @var Xls $objRead */
                $objRead = IOFactory::createReader('Xls');

                if (!$objRead->canRead($file)) {
                    $msg['code'] = 0;
                    $msg['tips'] = '只支持导入Excel文件!';
                    return $msg;
                    //throw new \Exception('只支持导入Excel文件！');
                }
            }

            /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
            empty($options) && $objRead->setReadDataOnly(true);
            /* 建立excel对象 */
            $obj = $objRead->load($file);
            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (isset($options['mergeCells'])) {
                /* 读取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }

            if (0 == $columnCnt) {
                /* 取得最大的列号 */
                $columnH = $currSheet->getHighestColumn();
                /* 兼容原逻辑，循环时使用的是小于等于 */
                $columnCnt = Coordinate::columnIndexFromString($columnH);
            }

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data = [];

            /* 读取内容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;

                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId = $cellName . $_row;
                    $cell = $currSheet->getCell($cellId);

                    if (isset($options['format'])) {
                        /* 获取格式 */
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式，公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }

                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

                    if (!empty($data[$_row][$cellName])) {
                        $isNull = false;
                    }
                }

                /* 判断是否整行数据为空，是的话删除该行数据 */
                if ($isNull) {
                    unset($data[$_row]);
                }
            }

            return $data;
        } catch (\Exception $e) {
            throw $e;
        }
    }


    /**
     * Excel导出
     *
     * @param array $datas 导出数据，格式[['name' => 'alibaba','age' => 18]]
     * @param string $fileName 导出文件名称
     * @param array $options 操作选项，例如：
     *                           bool   print       设置打印格式
     *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
     *                           array  setARGB     设置背景色，例如['A1', 'C1']
     *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
     *                           bool   setBorder   设置单元格边框
     *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
     *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
     *                           array  format      设置格式，整列设置，例如['A' => 'General']
     *                           array  alignCenter 设置居中样式，例如['A1', 'A2']
     *                           array  bold        设置加粗样式，例如['A1', 'A2']
     *                           string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
     * @param array $param 数据键名  ['0' => 'name','1' => 'age']
     */
    function exportExcel($datas, $param, $fileName = '', $options = [])
    {
        try {
            if (empty($datas)) {
                return false;
            }
            if (empty($param)) {
                return false;
            }
            //列名
            $rows = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
            set_time_limit(0);
            /** @var Spreadsheet $objSpreadsheet */
            $objSpreadsheet = new Spreadsheet();
            //设置默认文字居左，上下居中
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical' => Alignment::VERTICAL_CENTER,
                ],
            ];

            $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
            //设置Excel Sheet
            $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

            //打印设置
            /*if (isset($options['print']) && $options['print']) {
                //设置打印为A4效果
                $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
                //设置打印时边距
                $pValue = 1 / 2.54;
                $activeSheet->getPageMargins()->setTop($pValue / 2);
                $activeSheet->getPageMargins()->setBottom($pValue * 2);
                $activeSheet->getPageMargins()->setLeft($pValue / 2);
                $activeSheet->getPageMargins()->setRight($pValue / 2);
            }*/

            //行数据处理
            foreach ($datas as $sKey => $sItem) {
                //默认文本格式
                $pDataType = DataType::TYPE_STRING;
                //设置单元格格式
                /*if (isset($options['format']) && !empty($options['format'])) {
                    $colRow = Coordinate::coordinateFromString($sKey);
                    //存在该列格式并且有特殊格式
                    if (isset($options['format'][$colRow[0]]) &&
                        NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
                        $activeSheet->getStyle($sKey)->getNumberFormat()
                            ->setFormatCode($options['format'][$colRow[0]]);

                        if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
                            is_numeric(str_replace(['￥', ','], '', $sItem))) {
                            //数字格式转换为数字单元格
                            $pDataType = DataType::TYPE_NUMERIC;
                            $sItem     = str_replace(['￥', ','], '', $sItem);
                        }
                    } elseif (is_int($sItem)) {
                        $pDataType = DataType::TYPE_NUMERIC;
                    }
                }*/
                $sKey = $sKey + 1;
                for ($i = 0; $i < count($param); $i++) {
                    $activeSheet->setCellValueExplicit($rows[$i] . $sKey, $sItem[$param[$i]], $pDataType);
                }

                //存在:形式的合并行列，列入A1:B2，则对应合并
                /*if (false !== strstr($sKey, ":")) {
                    $options['mergeCells'][$sKey] = $sKey;
                }*/
            }

            unset($datas);

            //设置锁定行
            /*if (isset($options['freezePane']) && !empty($options['freezePane'])) {
                $activeSheet->freezePane($options['freezePane']);
                unset($options['freezePane']);
            }*/

            //设置宽度
            if (isset($options['setWidth']) && !empty($options['setWidth'])) {
                foreach ($options['setWidth'] as $swKey => $swItem) {
                    $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
                }

                unset($options['setWidth']);
            }

            //设置背景色
            /*if (isset($options['setARGB']) && !empty($options['setARGB'])) {
                foreach ($options['setARGB'] as $sItem) {
                    $activeSheet->getStyle($sItem)
                        ->getFill()->setFillType(Fill::FILL_SOLID)
                        ->getStartColor()->setARGB(Color::COLOR_YELLOW);
                }

                unset($options['setARGB']);
            }*/

            //设置公式
            /*if (isset($options['formula']) && !empty($options['formula'])) {
                foreach ($options['formula'] as $fKey => $fItem) {
                    $activeSheet->setCellValue($fKey, $fItem);
                }

                unset($options['formula']);
            }*/

            //合并行列处理
            /*if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
                $activeSheet->setMergeCells($options['mergeCells']);
                unset($options['mergeCells']);
            }*/

            //设置居中
            /*if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical'   => Alignment::VERTICAL_CENTER,
                    ],
                ];

                foreach ($options['alignCenter'] as $acItem) {
                    $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
                }

                unset($options['alignCenter']);
            }*/

            //设置加粗
            /*if (isset($options['bold']) && !empty($options['bold'])) {
                foreach ($options['bold'] as $bItem) {
                    $activeSheet->getStyle($bItem)->getFont()->setBold(true);
                }

                unset($options['bold']);
            }*/

            //设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列
            /*if (isset($options['setBorder']) && $options['setBorder']) {
                $border    = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN, // 设置border样式
                            'color'       => ['argb' => 'FF000000'], // 设置border颜色
                        ],
                    ],
                ];
                $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                $activeSheet->getStyle($setBorder)->applyFromArray($border);
                unset($options['setBorder']);
            }*/

            $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');

            if (!isset($options['savePath'])) {
                //直接导出Excel，无需保存到本地，输出07Excel文件
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header(
                    "Content-Disposition:attachment;filename=" . iconv(
                        "utf-8", "GB2312//TRANSLIT", $fileName
                    )
                );
                header('Access-Control-Allow-Origin:*'); // *代表允许任何网址请求
                header('Access-Control-Allow-Methods:POST,GET,OPTIONS,DELETE'); // 允许请求的类型
                header('Access-Control-Allow-Credentials: true'); // 设置是否允许发送 cookies
                header('Access-Control-Allow-Headers: Content-Type,Content-Length,Accept-Encoding,X-Requested-with, Origin'); // 设置允许自定义请求头的字段
                header('Cache-Control: max-age=0');//禁止缓存
                $savePath = 'php://output';
            } else {
                $savePath = $options['savePath'];
            }
            ob_clean();
            ob_start();
            $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
            $objWriter->save($savePath);
            //释放内存
            $objSpreadsheet->disconnectWorksheets();
            unset($objSpreadsheet);
            ob_end_flush();

            return true;
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * 每个sheet设置
     * @param $spreadsheet
     * @param $sheetIndex
     * @param $data
     */
    function handlingMultipleSheet($spreadsheet, $sheetIndex, $data)
    {
        // 创建sheet
        $spreadsheet->createSheet();
        $sheettstyle = $spreadsheet->getActiveSheet();
        // 设置当前的活动sheet
        $activeSheet = $spreadsheet->setActiveSheetIndex($sheetIndex);
        $sheettstyle->getDefaultRowDimension()->setRowHeight(30);//设置默认行高为15
        // 设置每个sheet中的名称 title
        $activeSheet->setCellValue('A1', $data['title']);
        // 这是你的数据键名
        $rows = $data['rows'][0];
        // 计算你所占的列数
        $columnsCount = count($rows);
        // 求k-v值的所占行数
        /*$infoNum = ceil(count($data['info']) / 2);*/
        // 下面的详细信息的开始行数
        //$infoStart = $infoNum + 2;
        $infoStart = 2;
        $cellName = [];
        for ($column = 0; $column < $columnsCount; $column++) {
            array_push($cellName, $this->intToChr($column));
        }
        // 设置标题栏的名称
        $sheet = $spreadsheet->getActiveSheet($sheetIndex)->setTitle($data['title']);
        // 合并标题栏单元格
        $spreadsheet->getActiveSheet($sheetIndex)->mergeCells('A1:' . $cellName[$columnsCount - 1] . '1');
        // 设置标题栏的字体大小
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A1')->getFont()->setSize(20);
        // 中部数据水平居中
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('B')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('C')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('D')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('E')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('F')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('G')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        // 中部数据垂直居中
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A1')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('B')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('C')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('D')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('E')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('F')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle('G')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        // 低部数据居中
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle($infoStart)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        // 低部数据标题栏加粗
        $spreadsheet->getActiveSheet($sheetIndex)->getStyle($infoStart)->getFont()->setSize(15);
        //$spreadsheet->getActiveSheet($sheetIndex)->getStyle($infoStart)->getFont()->setBold(true);

        // 低部数据信息循环
        foreach ($data['rows'] as $key => $item) {
            if($item[0]=='部门签章：'){
                $spreadsheet->getActiveSheet($sheetIndex)->mergeCells('B2:' . $cellName[$columnsCount - 1] . '2');
            }
            if($item[0]=='终端用户'){
                $spreadsheet->getActiveSheet($sheetIndex)->mergeCells('B8:' . $cellName[$columnsCount - 1] . '8');
            }

            //循环设置单元格：
            //$key+$infoStart,因为第一行是表头，所以写到表格时   从第数据行开始写
            for ($i = 0; $i < $columnsCount; $i++) {
                $sheet->setCellValue($this->intToChr($i) . ($key + $infoStart), $item[$i]);
                // 固定列宽
                $spreadsheet->getActiveSheet($sheetIndex)->getColumnDimension($this->intToChr($i))->setWidth(25);
                $spreadsheet->getActiveSheet($sheetIndex)->getStyle(($key + $infoStart))->getFont()->setSize(15);
                $spreadsheet->getActiveSheet($sheetIndex)->getStyle(($key + $infoStart))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $spreadsheet->getActiveSheet($sheetIndex)->getStyle(($key + $infoStart))->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            }
        }

        // 中闻数据处理
       /* $rowNumber = 1;
        $infoIndex = 0;
        foreach ($data['info'] as $key => $value) {
            if ($infoIndex % 2 == 0) {
                $rowNumber++;
                $infoCellName1 = 'A' . $rowNumber;
                $infoCellMegreRange = 'B' . $rowNumber . ':C' . $rowNumber;
                $infoCellName2 = 'B' . $rowNumber;
            } else {
                $infoCellName1 = 'D' . $rowNumber;
                $infoCellMegreRange = 'E' . $rowNumber . ':F' . $rowNumber;
                $infoCellName2 = 'E' . $rowNumber;
            }
            $spreadsheet->setActiveSheetIndex($sheetIndex)->setCellValue($infoCellName1, $key);
            $spreadsheet->getActiveSheet($sheetIndex)->mergeCells($infoCellMegreRange);
            $spreadsheet->setActiveSheetIndex($sheetIndex)->setCellValue($infoCellName2, $value);
            $infoIndex++;
        }*/
    }
    /**
     * 多sheet的导出
     * @param $datas
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    function exportExecl1($datas)
    {
        $name = '完成情况-' . date("Y-m-d", time());
        $spreadsheet = new Spreadsheet();
        foreach ($datas as $key => $data) {
            $this->handlingMultipleSheet($spreadsheet, $key, $data);
        }
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header(
            "Content-Disposition:attachment;filename=" . iconv(
                "utf-8", "GB2312//TRANSLIT", $name
            )
        );
        header('Access-Control-Allow-Origin:*'); // *代表允许任何网址请求
        header('Access-Control-Allow-Methods:POST,GET,OPTIONS,DELETE'); // 允许请求的类型
        header('Access-Control-Allow-Credentials: true'); // 设置是否允许发送 cookies
        header('Access-Control-Allow-Headers: Content-Type,Content-Length,Accept-Encoding,X-Requested-with, Origin'); // 设置允许自定义请求头的字段
        header('Cache-Control: max-age=0');//禁止缓存



       /* header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');*/
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * 数字转字母（类似excel列标）
     * @param $index
     * @param int $start
     * @return string
     */
    function intToChr($index, int $start = 65): string
    {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= intToChr(floor($index / 26) - 1);
        }
        return $str . chr($index % 26 + $start);
    }
}